package com.dongnam.quanly.BaoCaoKho;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.faces.application.FacesMessage;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.ManagedProperty;
import javax.faces.bean.SessionScoped;
import javax.faces.context.FacesContext;

import org.primefaces.event.SelectEvent;

import com.dongnam.quanly.common.model.HoaDonKhac;
import com.dongnam.quanly.web.UserBean;
import com.dongnam.quanly.web.util.DateFormatUtil;

@ManagedBean(name = "BAOCAOBEAN")
@SessionScoped
public class BAOCAOBEAN implements Serializable {
	private static final long serialVersionUID = 1L;
	@ManagedProperty(value = "#{UserBean}")
	private UserBean userBean;
	private HoaDonKhac hoadonkhac;
	private List<HoaDonKhac> listHD;
	private Date tuNgay;
	private Date denNgay;
	public void clear(){
		listHD = new ArrayList<HoaDonKhac>();
	}
	public BAOCAOBEAN() {
		super();
		listHD = new ArrayList<HoaDonKhac>();
	}

	public UserBean getUserBean() {
		return userBean;
	}

	public void setUserBean(UserBean userBean) {
		this.userBean = userBean;
	}

	public void showReport() {
		connectDatabase();
	}

	private void connectDatabase() {
		listHD = new ArrayList<HoaDonKhac>();
		String userName = "sa";
		String password = "Admin@123";// sqlserver123
		String url = "jdbc:jtds:sqlserver://10.0.1.14/CATLAIXD";// localhost
		String ng_tx = "";
		String tuNgays = "";
		String denNgays = "";
		SimpleDateFormat format = new SimpleDateFormat("MM-dd-yyyy");

		try {
			if (tuNgay != null || denNgay != null) {
				if (tuNgay.equals(denNgay)) {
					tuNgays = format.format(tuNgay);
					ng_tx = "ng_tx ='" + tuNgays + "' and ";
					// System.out.println(ng_tx);
				} else if (tuNgay.before(denNgay)) {
					tuNgays = format.format(tuNgay);
					denNgays=format.format(denNgay);
					ng_tx = "(ng_tx between '" + tuNgays + "' and '"+denNgays+"') and ";
				} 
			}
			Class.forName("net.sourceforge.jtds.jdbc.Driver");
			Connection conn = DriverManager.getConnection(url, userName,
					password);
			String sqlquery="select hd.so_hoadon,hd.ngay,hd.ng_tx,"
					+ "(CASE  WHEN hd.soxe IS NULL THEN ' ' else hd.soxe END)as soxe, "
					+ "(CASE WHEN hd.ng_nhan IS NULL THEN ' ' ELSE hd.ng_nhan END)as ng_nhan,"
					+ "CONVERT(VARCHAR(10),hd.ngaygio_tx,108) as gionhan,hd.ma_vthh,hd.sl_tx,hd.sl_tx15,hd.nhietdo,hd.titrong"
					+ " from v_thucxuatxd hd,dmsoxe dm where " + ng_tx
					+ "  dm.ma_ptvc='XB' and hd.soxe=dm.soxe  ";
			PreparedStatement ps = conn
					.prepareStatement(sqlquery);


			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				hoadonkhac = new HoaDonKhac(rs.getString(1), rs.getDate(2),
						rs.getDate(3), rs.getString(4), rs.getString(5),
						rs.getTime(6), rs.getString(7), rs.getFloat(8),
						rs.getFloat(9), rs.getFloat(10), rs.getFloat(11));
				// hoadonkhac.setSo_hoadon(rs.getString(1));
				// hoadonkhac.setNgay(rs.getDate(2));
				// hoadonkhac.setNg_tx(rs.getDate(3));
				listHD.add(hoadonkhac);
			}
			rs.close();
			ps.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void onDateSelectTuNgay(SelectEvent event) {
		FacesContext facesContext = FacesContext.getCurrentInstance();
		SimpleDateFormat format = new SimpleDateFormat("MM/dd/YYYY");
		facesContext.addMessage(null,
				new FacesMessage(FacesMessage.SEVERITY_INFO, "Date Selected",
						format.format(event.getObject())));
		tuNgay = (Date) event.getObject();
		// System.out.println(ngayPhatHanhUpload);

	}

	public void onDateSelectDenNgay(SelectEvent event) {
		FacesContext facesContext = FacesContext.getCurrentInstance();
		SimpleDateFormat format = new SimpleDateFormat("MM/dd/YYYY");
		facesContext.addMessage(null,
				new FacesMessage(FacesMessage.SEVERITY_INFO, "Date Selected",
						format.format(event.getObject())));
		denNgay = (Date) event.getObject();
		// System.out.println(ngayPhatHanhUpload);

	}

	public List<HoaDonKhac> getListHD() {
		return listHD;
	}

	public void setListHD(List<HoaDonKhac> listHD) {
		this.listHD = listHD;
	}

	public Date getTuNgay() {
		return tuNgay;
	}

	public void setTuNgay(Date tuNgay) {
		this.tuNgay = tuNgay;
	}

	public Date getDenNgay() {
		return denNgay;
	}

	public void setDenNgay(Date denNgay) {
		this.denNgay = denNgay;
	}

}
